package org.light.framework.orm;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

import org.light.framework.bean.ResultColumn;
import org.light.framework.bean.SearchArgs;
import org.light.framework.bean.UpdateColumn;
import org.light.framework.dao.DbType;
import org.light.framework.dao.SQLHelper;
import org.light.framework.orm.annotation.JoinBehaviour;
import org.light.framework.util.CollectionUtil;
import org.light.framework.util.ReflectionUtil;
import org.light.framework.util.StringUtil;

/**
 * SQL语句构建类
 * 
 * @author Administrator
 *
 */
public class SQLBuilder {

	/**
	 * 拼接新增的SQL语句
	 * 
	 * @param obj
	 * @param tableName
	 *            表名，为null时，方法中反射获取
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static <T> String buildInsertSql(T obj, String tableName, ArrayList<Object> params) throws Exception {

		if (tableName == null)
			tableName = DbMapper.getTableName(obj.getClass());
		ArrayList<ColumnInfo> columns = DbMapper.getColumns(obj.getClass());
		HashMap<BelongsInfo, Object> belongsValue = DbMapper.getBelongs(obj);

		StringBuilder builder = new StringBuilder();
		StringBuilder builderV = new StringBuilder();

		builder.append("INSERT INTO " + tableName);
		builder.append("(");

		// 普通列
		for (ColumnInfo c : columns) {

			builder.append(c.name + ",");
			builderV.append("?,");

			Field field=obj.getClass().getDeclaredField(c.propertyName);
			field.setAccessible(true);
			Object value = field.get(obj);
			params.add(DbValueConverter.java2Jdbc(value));
		}

		// 外键列
		for (BelongsInfo c : belongsValue.keySet()) {

			builder.append(c.name + ",");
			builderV.append("?,");

			Object fkValue = belongsValue.get(c);
			params.add(fkValue);
		}

		builder.deleteCharAt(builder.length() - 1);
		builderV.deleteCharAt(builderV.length() - 1);

		builder.append(")");
		builder.append(" values");
		builder.append(" (");
		builder.append(builderV.toString());
		builder.append(") ;\r\n");

		return builder.toString();
	}
	
	
	/**
	 * 拼接批量新增的SQL语句(MySql适用)
	 * 
	 * @param objs
	 * @param tableName
	 *            表名，为null时，方法中反射获取
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static <T> String buildInsertSql4MySql(List<T> objs, String tableName, ArrayList<Object> params) throws Exception {

		
		if(CollectionUtil.isEmpty(objs)){
			throw new Exception("没有要批量插入的数据！");
		}
		
		T obj=objs.get(0);
		
		if (tableName == null)
			tableName = DbMapper.getTableName(obj.getClass());
		ArrayList<ColumnInfo> columns = DbMapper.getColumns(obj.getClass());
		HashMap<BelongsInfo, Object> belongsValue = DbMapper.getBelongs(obj);

		StringBuilder builder = new StringBuilder();
		StringBuilder builderV = new StringBuilder();

		builder.append("INSERT INTO " + tableName);
		builder.append("(");

		// 普通列
		for (ColumnInfo c : columns) {

			builder.append(c.name + ",");
		}

		// 外键列
		for (BelongsInfo c : belongsValue.keySet()) {

			builder.append(c.name + ",");
		}

		builder.deleteCharAt(builder.length() - 1);
		

		builder.append(")");
		builder.append(" values");
		
		for (T model : objs) {
			
			builderV.append("(");
			// 普通列
			for (ColumnInfo c : columns) {

				builderV.append("?,");

				Field field=model.getClass().getDeclaredField(c.propertyName);
				field.setAccessible(true);
				Object value = field.get(model);
				params.add(DbValueConverter.java2Jdbc(value));
			}

			// 外键列
			for (BelongsInfo c : belongsValue.keySet()) {

				builderV.append("?,");

				Object fkValue = belongsValue.get(c);
				params.add(fkValue);
			}
			
			builderV.deleteCharAt(builderV.length() - 1);
			builderV.append("),");
		}
		
		builderV.deleteCharAt(builderV.length() - 1);
		builder.append(builderV.toString());

		return builder.toString();
	}

	/**
	 * 拼接更新SQL语句
	 * 
	 * @param obj
	 * @param tableName
	 *            表名
	 * @param primaryKey
	 *            主键
	 * @param columns
	 *            普通列
	 * @param updateColumn
	 *            需要更新的列
	 * @param params
	 *            输出用
	 * @return
	 * @throws Exception
	 */
	public static <T> String buildUpdateSql(T obj, String tableName, ColumnInfo primaryKey,
			ArrayList<ColumnInfo> columns, UpdateColumn updateColumn, ArrayList<Object> params) throws Exception {

		if (updateColumn == null)
			updateColumn = new UpdateColumn(obj.getClass());
		if (tableName == null)
			tableName = DbMapper.getTableName(obj.getClass());
		if (primaryKey == null)
			primaryKey = DbMapper.getPrimaryKey(obj.getClass());
		if (columns == null)
			columns = DbMapper.getColumns(obj.getClass());

		HashMap<BelongsInfo, Object> belongs = DbMapper.getBelongs(obj);

		StringBuilder builder = new StringBuilder();
		builder.append(" UPDATE " + tableName);
		builder.append(" SET ");

		// 普通列
		for (ColumnInfo c : columns) {

			if (c.readOnly || !updateColumn.include(c.propertyName))
				continue;

			builder.append(c.name + "=?,");
			Field field=obj.getClass().getDeclaredField(c.propertyName);
			field.setAccessible(true);
			Object value=field.get(obj);
			params.add(DbValueConverter.java2Jdbc(value));
		}

		// 外键列
		for (BelongsInfo c : belongs.keySet()) {

			if (c.readOnly || (updateColumn != null && !updateColumn.include(c.propertyName)))
				continue;

			builder.append(c.name + "=?,");
			params.add(belongs.get(c));
		}

		builder.deleteCharAt(builder.length() - 1);

		Field field = obj.getClass().getDeclaredField(primaryKey.propertyName);
		field.setAccessible(true);
		Object value = field.get(obj);

		// 主键参数
		params.add(value);

		builder.append(" WHERE " + primaryKey.name + "=? ;");
		return builder.toString();
	}

	/**
	 * 拼接删除SQL
	 * 
	 * @param obj
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static <T> String buildDeleteSql(String tableName, ColumnInfo primaryKey, Object value,
			ArrayList<Object> params) throws Exception {

		StringBuilder builder = new StringBuilder();
		builder.append(" DELETE FROM " + tableName);

		params.add(value);

		builder.append(" WHERE " + primaryKey.name + "=? ;");
		return builder.toString();
	}
	
	public static <T> String buildDeleteSql(String tableName, ColumnInfo primaryKey, List<Object> values,
			List<Object> params) throws Exception {

		StringBuilder builder = new StringBuilder();
		builder.append(" DELETE FROM " + tableName);
		
		params.addAll(values);

		builder.append(" WHERE " + primaryKey.name + " IN (");
		
		int length=values.size();
		for (int i = 0; i < length; i++) {
			builder.append("?,");
		}
		builder.deleteCharAt(builder.length()-1);
		builder.append(");");
		return builder.toString();
	}
	

	/**
	 * 拼接查询SQL
	 * 
	 * @param type
	 * @param findOne
	 * @param resultColumn
	 * @return
	 * @throws Exception
	 */
	public static String buildSelectSql(Class<?> type, boolean findOne, ResultColumn resultColumn) throws Exception {

		DbType dbType = SQLHelper.GetDbType();
		String top = findOne && dbType == DbType.SqlServer ? " TOP(1) " : "";

		StringBuilder builder = new StringBuilder();
		builder.append(top);

		if (resultColumn == null)
			resultColumn = new ResultColumn();

		HashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(type);

		if (dicBelongs != null && !dicBelongs.isEmpty()) {

			ArrayList<ColumnInfo> tableColumns = DbMapper.getColumns(type, 0);
			String tableNameAlias = DbMapper.PRIMARY_TABLE_ALIAS;
			// 主表
			for (ColumnInfo c : tableColumns) {

				if (!resultColumn.include(c.propertyName))
					continue;

				String cname = tableNameAlias + "." + c.name;
				String calias = tableNameAlias + "_" + c.name;
				builder.append(cname + " AS " + calias);
				builder.append(",");
			}

			// 关联表
			for (String propertyName : dicBelongs.keySet()) {
				// 需要忽略的关联属性
				if (!resultColumn.include(propertyName))
					continue;

				// SELECT
				BelongsInfo bc = dicBelongs.get(propertyName);

				int cascade = propertyName.split(".").length;
				ArrayList<ColumnInfo> columns = DbMapper.getColumns(bc.entityType, cascade); // 获取关联实体
																								// 数据库映射信息
				for (ColumnInfo c : columns) {
					String belongsPropertyName = bc.propertyName + "." + c.propertyName;

					if (!resultColumn.include(belongsPropertyName))
						continue;

					// t1.ID AS t1_ID , t1.Name AS t1_Name
					String cname = bc.alias + "." + c.name;
					String calias = bc.alias + "_" + c.name;
					builder.append(cname + " AS " + calias);
					builder.append(",");
				}
			}
		} else {

			if (resultColumn.isEmpty()) {
				builder.append("  *  ");
			} else {
				// 普通列
				ArrayList<ColumnInfo> tableColumns = DbMapper.getColumns(type, 0);
				for (ColumnInfo c : tableColumns) {
					if (!resultColumn.include(c.propertyName))
						continue;

					String cname = c.name;
					String calias = c.name;
					builder.append(cname + " AS " + calias);
					builder.append(",");
				}
			}
		}

		if (builder.length() > 0) {
			builder.deleteCharAt(builder.length() - 1);
		}
		return builder.toString();
	}

	/**
	 * 拼接FROM子句
	 * 
	 * @param type
	 * @param resultColumn
	 * @return
	 * @throws Exception
	 */
	public static String buildFromSql(Class<?> type, ResultColumn resultColumn, SearchArgs searchArgs)
			throws Exception {

		if (resultColumn != null && searchArgs != null) {
			List<String> searchFields = searchArgs.getFields();
			// 当查询结果项不为空，且包含查询项时，把查询项，添加至查询列
			if (!resultColumn.isEmpty() && CollectionUtil.isNotEmpty(searchFields)) {
				for (String propertyName : searchFields)
					resultColumn.add(propertyName);
			}
		}

		String sql = "";
		String tableName = DbMapper.getTableName(type);

		HashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(type);
		if (dicBelongs != null && !dicBelongs.isEmpty()) {

			String tableNameAlias = DbMapper.PRIMARY_TABLE_ALIAS;
			StringBuilder builderFrom = new StringBuilder();
			builderFrom.append(tableName + " " + tableNameAlias);

			// 关联表
			
			// 先对key排序，避免先引用了表的列，再join表
			List<String> list = new ArrayList<String>(dicBelongs.keySet());
			Collections.sort(list, new Comparator<String>() {
				public int compare(String o1, String o2) {
					return o1.compareTo(o2);
				}
			});
			
			for (String belongsPropertyName : list) {
				if (resultColumn != null) {
					if (!resultColumn.include(belongsPropertyName))
						continue;
				}

				BelongsInfo bc = dicBelongs.get(belongsPropertyName);
				String tname = DbMapper.getTableName(bc.entityType);

				if (bc.joinBehaviour == JoinBehaviour.Inner)
					builderFrom.append(" JOIN " + tname + " " + bc.alias);
				else
					builderFrom.append(" LEFT JOIN " + tname + " " + bc.alias);

				String belongsFK = bc.name;
				String belongsPK = DbMapper.getPrimaryKey(bc.entityType).name;
				String onAlias = !StringUtil.isNullOrEmpty(bc.aliasReference) ? bc.aliasReference : tableNameAlias;

				builderFrom.append(" ON " + onAlias + "." + belongsFK + "=" + bc.alias + "." + belongsPK);
			}
			sql = builderFrom.toString();

		} else {
			sql = tableName;
		}
		return sql;
	}

	/// <summary>
	/// 拼接多对多关联表保存SQL
	/// </summary>
	/// <param name="obj">对象</param>
	/// <param name="primaryKey">主键信息</param>
	/// <param name="action">操作类型</param>
	/// <param name="updateColumn">需要更新的列</param>
	/// <returns></returns>
	/**
	 * 拼接多对多关联表保存SQL
	 */
	public static <T> String buildManyToManySql(T obj, ColumnInfo primaryKey, ManyToManyAction action,
			UpdateColumn updateColumn) {

		StringBuilder builder = new StringBuilder();
		try {
			HashMap<String, ManyToManyInfo> dicManyToMany = DbMapper.getManyToMany(obj.getClass());
			if (dicManyToMany != null && dicManyToMany.size() > 0) {
				for (String key : dicManyToMany.keySet()) {

					ManyToManyInfo many = dicManyToMany.get(key);

					if (updateColumn != null && !updateColumn.include(key))
						continue;

					Object value = ReflectionUtil.getPropertyValue(obj, primaryKey.propertyName);

					if (action != ManyToManyAction.Create) { // 修改或删除时，先清除已有关联

						builder.append(
								" DELETE FROM " + many.table + " WHERE " + many.columnKey + "='" + value + "'; ");
					}

					if (action != ManyToManyAction.Delete) { // 新增或修改

						List<?> objsRef = (List<?>) ReflectionUtil.getPropertyValue(obj, key);

						for (Object objRef : objsRef) {
							Object objRefID = ReflectionUtil.getPropertyValue(objRef, primaryKey.propertyName);

							builder.append(String.format(" INSERT INTO %s(%s,%s,%s) VALUES('%s','%s','%s');",
									many.table, many.tableKey, many.columnKey, many.columnRef, UUID.randomUUID(), value,
									objRefID));
						}
					}
				}
			}
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		}
		return builder.toString();
	}
}
